package inl.resilienceManager.qmul.gemom;

import java.sql.*; 
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Random;
import java.util.StringTokenizer;
import java.lang.*;

import resilienceManager.qmul.gemom.Gbroker;
import resilienceManager.qmul.gemom.namespace;

/**
 * Saving the information in the database
 *  
 * COPYRIGHT DISCLAIMER
 * Synopsis: All the source codes presented in this document are intellectual
 * properties of Queen Mary, University of London.
 * 
 * @year 2010
 * @author Peng Jiang, John Bigham, Jiayi Wu Jinfu Wang------------QMUL
 * @version 1.0 
 */

public class TableSaver {
	// the driver of mysql 
	private final static String driver= "com.mysql.jdbc.Driver";
	// the url of database
	private final static String url="jdbc:mysql://127.0.0.1:3306/test";
	// the username
	private final static String user="root";
	// the password
	private final static String password = "admin"; 
	// the table name used for the variance
	private final static String tablename = "varTable";
//	table of mean rates
	private final static String meansdevtabname = "meansdev";
//	solution table
	private final static String soltabname = "soltab";
	private final static String brokerstabname = "brokers";
	private final static String nstabname = "namespace"; //+tabnum
	private final static String itemtabname = "item"; //+tabnum
	// the number of variance
	private  static int numV=31;
	

	// according to the number of variance, build the table
	public static void Creat(){

		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}

			Statement statement = conn.createStatement();

			String sql="create table "+tablename+" ( id int not null, ";

			String commaAndSpace = " float, ";

			for(int i=1; i<numV+1; i++)
			{
				String table="N"+i;

				sql=sql+table+commaAndSpace;

			}
			sql=sql+"primary key(id));";
			System.out.println(sql);
			statement.executeUpdate(sql); 

			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}
	}
	
	

	
	
	
//	TODO create mean rates table


//FIXME: no test.vartable

	public static void Saving(float[][] vT)
	{
		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}
			
			Statement statement;
			String desql="delete from "+tablename;
			statement= conn.createStatement();
System.out.println("" + desql);
			statement.executeUpdate(desql); 
			

			for(int row=1; row< numV+1; row++){
				statement= conn.createStatement();

				String sql="insert into "+tablename+" (id";


				String commaAndSpace = ", ";

				for(int i=1; i<numV+1; i++)
				{
					String table="N"+i;
					sql=sql+commaAndSpace+table;
				}
				sql=sql+") values ("+row;

				for(int i=0; i<numV; i++)
				{
					sql=sql+commaAndSpace+vT[row-1][i];
				}

				sql=sql+");";


			//	System.out.println(sql);
				statement.executeUpdate(sql); 
			}
			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}
	}


	
	public static Object[][] Loading(){
		Object[][] vT = null;

		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}


			Statement statement = conn.createStatement();
			String sql="select * from "+tablename;
			ResultSet rs = statement.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int noOfColumns = rsmd.getColumnCount();
			vT=new Object[noOfColumns-1][noOfColumns-1];
			
			int row=0;

			while(rs.next())
			{
				for(int i=0; i<noOfColumns-1; i++){
// XXX why rs.getObject(i+2)					
					vT[row][i]=rs.getObject(i+2);
				}
				row++;
			}

			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}
		
		return vT;
/*
		Object[][] vT = new Object[numV][numV];

		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}


			Statement statement = conn.createStatement();
			String sql="select * from "+tablename;
			ResultSet rs = statement.executeQuery(sql);
			
			int row=0;

			while(rs.next())
			{
				for(int i=0; i<numV; i++){
// XXX why rs.getObject(i+2)					
					vT[row][i]=rs.getObject(i+2);
				}
				row++;
			}

			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}

		return vT;
*/
	}
	
	
	
//	TODO save and load rates

	
	
	
	
	
	public static float[][] OtoF(Object[][] dataTable)
	{
		float[][] data;
		
		int num= dataTable.length;
		
		data= new float[num][num];
		
		for(int i=0; i<num; i++)
		{
			for(int j=0; j<num; j++)
			{
				data[i][j]= Float.parseFloat(dataTable[i][j].toString());
			}
		}
		
		return data;
		
	}
	

	/**
	 * random generator a float array in 0.0-1.0
	 * 
	 * @return data: float array
	 * */

	public static float buildRand(int range){

		Random generator = new Random();

		float data;

		data= generator.nextFloat()*range;


		return data;

	}
	/**
	 * 
	 * Random generator a 2D matrix in 0.0-1.0
	 * 
	 * @return data: flat[][]
	 * **/
	public static float[][] getRandTable(){
		
		float[][] data= new float[numV][numV];
		
		for(int i=0;i<numV; i++)
			for(int j=0; j<numV; j++)
				data[i][j]=0;
		
		for(int i=0; i<numV; i++)
		{
			for(int j=0; j<numV; j++)
			{	
				if(data[i][j]==0)
				{
					data[i][j]=buildRand(3);
					data[j][i]=data[i][j];
				}
			}
		}
		
		return data;
	}
	
	public static float[][] getRandRates(int inum){
		float[][] rates= new float[numV][inum];
		
		for(int i=0;i<numV; i++)
			for(int j=0; j<inum; j++)
				rates[i][j]=0;
		
		for(int i=0; i<numV; i++)
			for(int j=0; j<inum; j++)
		{
			if(rates[i][j]==0)
				rates[i][j]=buildRand(300);

		}
		return rates;
	}
//	
	public static void createMatrixTab(float[][] m){

		drop(tablename);
		int size=m.length;
		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}

			Statement statement = conn.createStatement();

			String sql="create table if not exists "+tablename+" ( id int not null, ";

			String commaAndSpace = " float, ";

			for(int i=1; i<size+1; i++)
			{
				String table="N"+i;
				sql=sql+table+commaAndSpace;

			}
			sql=sql+"primary key(id));";
			System.out.println(sql);
			statement.executeUpdate(sql); 

			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}
	}
	
	public static void saveMatrix(float[][] vT)
	{
		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}
			
			Statement statement;
			String desql="delete from "+tablename;
			statement= conn.createStatement();
System.out.println("" + desql);
			statement.executeUpdate(desql); 
			

			for(int row=1; row< vT.length+1; row++){
				statement= conn.createStatement();

				String sql="insert into "+tablename+" (id";
				String commaAndSpace = ", ";

				for(int i=1; i<vT.length+1; i++)
				{
					String table="N"+i;
					sql=sql+commaAndSpace+table;
				}
				sql=sql+") values ("+row;

				for(int i=0; i<vT.length; i++)
				{
					sql=sql+commaAndSpace+vT[row-1][i];
				}

				sql=sql+");";


			//	System.out.println(sql);
				statement.executeUpdate(sql); 
			}
			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}
	}
	
	
	public static float[][] loadMatrix(){
//		Object[][] vT = new Object[numV][numV];
		Object[][] vT = null;

		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}


			Statement statement = conn.createStatement();
			String sql="select * from "+tablename;
			ResultSet rs = statement.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int noOfColumns = rsmd.getColumnCount();
			vT=new Object[noOfColumns-1][noOfColumns-1];
			
			int row=0;

			while(rs.next())
			{
				for(int i=0; i<noOfColumns-1; i++){
// XXX why rs.getObject(i+2)					
					vT[row][i]=rs.getObject(i+2);
				}
				row++;
			}

			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}

		return OtoF(vT);

	}

	
	
	
	
////	after load the scenario, create solution table
	public static void createSoltab(Gbroker[] bs){
		int len=bs.length;
		int brokerIDs[] = new int[bs.length];
		try{
			drop(soltabname);
		} 
		catch(Exception e){
			e.printStackTrace(); 
		}
		
//		FIXME: DROP the existing table;
		
//		for (Gbroker b : bs){
//			b.getID()
//		}
//		create table
//		create table sol0(id int not null, b1 varchar(20), b2 varchar(20), b3 varchar(20), s4 varchar(20), risk float,primary key(id));
//		11111
//		01111
//		10111
//		
//	}

		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}

			Statement statement = conn.createStatement();

			String sql="create table "+soltabname+" ( id varchar(30) not null, ";

			String commaAndSpace = " varchar(30), ";

			for(int i=0; i<len; i++)
			{
				brokerIDs[i]=bs[i].getID();
			}
			Arrays.sort(brokerIDs);
			for (int i=0; i<len;i++){
//				b1
				sql=sql+"b"+brokerIDs[i]+commaAndSpace;
			}
			
			
			sql=sql+"primary key(id));";
			System.out.println(sql);
			statement.executeUpdate(sql); 
			
			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}


	}
	
	public static void saveSol(Sols ss)
	{
		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}
			
			Statement statement;
			String desql="delete from "+soltabname+ " where id="+ ss.getSolKey() +";";
			statement= conn.createStatement();
System.out.println("" + desql);
			statement.executeUpdate(desql); 
			
			String sql="insert into " + soltabname + " (id ";

			String commaAndSpace = ", ";
			
			for(solution s : ss.getSol()){
//				statement= conn.createStatement();
// XXX deleted the "b" in id
				sql= sql + commaAndSpace +"b" +s.getGID();
					
			}
			
			sql=sql+" ) VALUES ('" + ss.getSolKey()+"'";

			for(solution s: ss.getSol()){
				sql=sql+  commaAndSpace + "'"+ s.toString() +"'" ;
			}
			sql=sql+");";
				System.out.println(sql);
			statement= conn.createStatement();
			statement.executeUpdate(sql); 
			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}
	}
	
	
public static void saveEmptySol(Gbroker[] allbrokers, Gbroker[] deadbrokers, Gbroker[] livebrokers) {
			Sols ss =new Sols(allbrokers, deadbrokers);
			try { 
				// load the driver
				Class.forName(driver); 
	
				// connect with database
				Connection conn = DriverManager.getConnection(url, user, password); 
	
				if(!conn.isClosed()) 
				{
					//System.out.println("Succeeded connecting to the Database!");	
				}
				
				Statement statement;
				String desql="delete from "+soltabname+ " where id='"+ ss.getSolKey() +"';";
				statement= conn.createStatement();
	System.out.println("" + desql);
				statement.executeUpdate(desql); 
				
				String sql="insert into " + soltabname + " (id ";
	
				String commaAndSpace = ", ";
				
				for(Gbroker b : livebrokers){
	//				statement= conn.createStatement();
	// XXX deleted the "b" in id
					sql= sql + commaAndSpace +"b" +b.getID();
						
				}
				
				sql=sql+" ) VALUES ('" + ss.getSolKey()+"'";
	
				for(Gbroker b : livebrokers){
					sql=sql+  commaAndSpace + "''" ;
				}
				sql=sql+");";
					System.out.println(sql);
				statement= conn.createStatement();
				statement.executeUpdate(sql); 
				conn.close(); 
	
			} catch(Exception e) { 
	
				e.printStackTrace(); 
			}
		}






public static String outputSol(String id){
			String s = "";
			
			try { 
				// load the driver
				Class.forName(driver); 
	
				// connect with database
				Connection conn = DriverManager.getConnection(url, user, password); 
	
				if(!conn.isClosed()) 
				{
					//System.out.println("Succeeded connecting to the Database!");	
				}
	
				PreparedStatement statement = conn.prepareStatement(
		        "SELECT * FROM "+soltabname+  " WHERE id = ? ");
	//	updateSales.setInt(1, 75); 
	//	updateSales.setString(2, "Colombian"); 
	//	updateSales.executeUpdate():
	//			Statement statement = conn.createStatement();
	//			String sql="select * from "+soltabname + " where id = " + id +";";
	//			statement.setString(1, soltabname);
				statement.setString(1,	id);
				ResultSet rs = statement.executeQuery();
	//			int row=0;
				ResultSetMetaData rsmd = rs.getMetaData();
				int noOfColumns = rsmd.getColumnCount();
				while(rs.next()){
					for(int i=2; i<noOfColumns+1; i++){
						System.out.println(rs.getString(i));
					}
					String a = rs.getString("b1");
					if (a=="" || a==null || a.length()==0){
						System.out.println("a is empty, a==null");
					}
	//				System.out.println("a is "+a);
				}
	
				conn.close(); 
	
			} catch(Exception e) { 
	
				e.printStackTrace(); 
			}
	
			return s;
	
		}

////after load the scenario, create solution table
public static void createBrokersTab(){
//	int len=bs.length;
//	int brokerIDs[] = new int[bs.length];
//	try{
//		drop(brokerstabname);
//	} 
//	catch(Exception e){
//		e.printStackTrace(); 
//	}
	
//	FIXME: DROP the existing table;
	
//	for (Gbroker b : bs){
//		b.getID()
//	}
//	create table
//	create table sol0(id int not null, b1 varchar(20), b2 varchar(20), b3 varchar(20), s4 varchar(20), risk float,primary key(id));
//	11111
//	01111
//	10111
//	
//}

	try { 
		// load the driver
		Class.forName(driver); 

		// connect with database
		Connection conn = DriverManager.getConnection(url, user, password); 

		if(!conn.isClosed()) 
		{
			//System.out.println("Succeeded connecting to the Database!");	
		}

		Statement statement = conn.createStatement();

		String sql="create table if not exists "+brokerstabname+" ( brokerid int not null, ";

//		String commaAndSpace = " varchar(60), ";
		sql=sql+ "cap float, " +"ip varchar(50), " + "th float, ";

//		for(int i=0; i<len; i++)
//		{
//			brokerIDs[i]=bs[i].getID();
//		}
//		Arrays.sort(brokerIDs);
//		for (int i=0; i<len;i++){
////			b1
//			sql=sql+"b"+brokerIDs[i]+commaAndSpace;
//		}
		
		
		sql=sql+"primary key(brokerid));";
		System.out.println(sql);
		statement.executeUpdate(sql); 
		
		conn.close(); 

	} catch(Exception e) { 

		e.printStackTrace(); 
	}


}

public static void saveBrokers(Gbroker[] bs)
{
	try { 
		// load the driver
		Class.forName(driver); 

		// connect with database
		Connection conn = DriverManager.getConnection(url, user, password); 

		if(!conn.isClosed()) 
		{
			//System.out.println("Succeeded connecting to the Database!");	
		}
		
		Statement statement;
		String desql="delete from "+brokerstabname+ ";";
		statement= conn.createStatement();
System.out.println("" + desql);
		statement.executeUpdate(desql); 
		
		PreparedStatement stmt = conn.prepareStatement(
		        "insert into " + brokerstabname + " (brokerid, cap, ip) values (?, ?, ?)" );
		
		for(Gbroker b : bs){
			stmt.clearParameters();
			stmt.setInt(1, b.getID());
			stmt.setFloat(2, (float)b.getCAP());
			stmt.setString(3, b.getIP());
			stmt.execute();
		}
		
		

		conn.close(); 

	} catch(Exception e) { 

		e.printStackTrace(); 
	}
}

public static Gbroker[] loadBrokers(){
	Gbroker[] bs = null;
	ArrayList<Gbroker> ba=new ArrayList<Gbroker>();
	try { 
		// load the driver
		Class.forName(driver); 
		
		// connect with database
		Connection conn = DriverManager.getConnection(url, user, password); 
		
		if(!conn.isClosed()) 
		{
			//System.out.println("Succeeded connecting to the Database!");	
		}
		
		PreparedStatement statement = conn.prepareStatement(
		        "SELECT * FROM "+brokerstabname,  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	//	updateSales.setInt(1, 75); 
	//	updateSales.setString(2, "Colombian"); 
	//	updateSales.executeUpdate():
	//			Statement statement = conn.createStatement();
	//			String sql="select * from "+soltabname + " where id = " + id +";";
	//			statement.setString(1, soltabname);
//				statement.setInt(1,	id);
				ResultSet rs = statement.executeQuery();
				ResultSetMetaData rsmd = rs.getMetaData();
				int noOfColumns = rsmd.getColumnCount();
				// Create a scrollable result set
			    
				
//				Statement stmt = connection.createStatement(
//			        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//			    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

			    // Move to the end of the result set
			    rs.last();
			    // Get the row number of the last row which is also the row count
			    int rowCount = rs.getRow();
			    rs.beforeFirst();
				bs = new Gbroker[rowCount];
				
				int row =0;
				while(rs.next()){
					bs[row]=(new Gbroker(rs.getInt("brokersid"), rs.getFloat("cap"), rs.getString("ip")));
					row++;
	//				System.out.println("a is "+a);
				}
//		Statement statement = conn.createStatement();
//		String sql="select * from "+meansdevtabname;
//		ResultSet rs = statement.executeQuery(sql);
//		
//		int row=0;
//		
//		while(rs.next())
//		{
//			for(int i=0; i<numV; i++){
//				
//				vT[row][i]=rs.getObject(i+2);
//			}
//			row++;
//		}
//		
		conn.close(); 
	
		
	} catch(Exception e) { 
		
		e.printStackTrace(); 
	}
	
	Arrays.sort(bs, new Comparator<Gbroker>() {
	    public int compare(Gbroker o1, Gbroker o2) {
	        return o1.getID()-o2.getID();
	    }});
	
	return bs;
	
}


 public static int[] getBrokerIDs(String[] brokerIPs){
	 int[] ids= new int[brokerIPs.length];
	 try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}

			Statement statement = conn.createStatement();
			
			 String sql = "select brokerid from " + brokerstabname +" where ip in (";
			 for(int i=0; i<brokerIPs.length;i++){
				 sql=sql + "'"+brokerIPs[i]+"'";
				 if (i!=brokerIPs.length-1){
					 sql=sql+",";
				 }
				 else{
					 sql=sql+");";
				 }
				 
			 }
			 ResultSet rs = statement.executeQuery(sql);
			 
			 int row=0;
			 while(rs.next()){
				 ids[row]=rs.getInt("brokerid");
				 row++;
			 }
			 
				conn.close(); 
				
				
		} catch(Exception e) { 
			
			e.printStackTrace(); 
		}
		
	 Arrays.sort(ids);
	 return ids;
 }
 
 public static int[] getAllBrokerIDs(){
	 
	 int[] ids = null;
	 
	 try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}

			Statement statement = conn.createStatement();
			
			 String sql = "select brokerid from brokers;";

			 ResultSet rs = statement.executeQuery(sql);
			    rs.last();
			    // Get the row number of the last row which is also the row count
			    int rowCount = rs.getRow();

				 ids = new int[rowCount];
			    rs.beforeFirst();
			 int row=0;
			 while(rs.next()){
				 ids[row]=rs.getInt("brokerid");
				 row++;
			 }
			 
				conn.close(); 
				
				
		} catch(Exception e) { 
			
			e.printStackTrace(); 
		}
		
	 Arrays.sort(ids);
	 return ids;
 }
 public static HashMap<String, String> getMapIDIP(){
	 
	 HashMap<String, String> idip = new HashMap<String, String>();
	 
	 try { 
		 // load the driver
		 Class.forName(driver); 
		 
		 // connect with database
		 Connection conn = DriverManager.getConnection(url, user, password); 
		 
		 if(!conn.isClosed()) 
		 {
			 //System.out.println("Succeeded connecting to the Database!");	
		 }
		 
		 Statement statement = conn.createStatement();
		 
		 String sql = "select * from "+ brokerstabname +";";
		 
		 ResultSet rs = statement.executeQuery(sql);
//		 rs.last();
//		 // Get the row number of the last row which is also the row count
//		 int rowCount = rs.getRow();
//		 
//		 ids = new int[rowCount];
//		 rs.first();
//		 int row=0;
		 while(rs.next()){
			 idip.put(""+rs.getInt("brokerid"), rs.getString("ip"));
		 }
		 
		 conn.close(); 
		 
		 
	 } catch(Exception e) { 
		 
		 e.printStackTrace(); 
	 }
	 
	 return idip;
 }
 public static HashMap<String, String> getMapIPID(){
	 
	 HashMap<String, String> ipid = new HashMap<String, String>();
	 
	 try { 
		 // load the driver
		 Class.forName(driver); 
		 
		 // connect with database
		 Connection conn = DriverManager.getConnection(url, user, password); 
		 
		 if(!conn.isClosed()) 
		 {
			 //System.out.println("Succeeded connecting to the Database!");	
		 }
		 
		 Statement statement = conn.createStatement();
		 
		 String sql = "select * from "+ brokerstabname +";";
		 
		 ResultSet rs = statement.executeQuery(sql);
//		 rs.last();
//		 // Get the row number of the last row which is also the row count
//		 int rowCount = rs.getRow();
//		 
//		 ids = new int[rowCount];
//		 rs.first();
//		 int row=0;
		 while(rs.next()){
			 ipid.put( rs.getString("ip"),""+rs.getInt("brokerid"));
		 }
		 
		 conn.close(); 
		 
		 
	 } catch(Exception e) { 
		 
		 e.printStackTrace(); 
	 }
	 
	 return ipid;
 }
 public static HashMap<String, String> getMapIDNS(){
	 
	 HashMap<String, String> idns = new HashMap<String, String>();
	 
	 try { 
		 // load the driver
		 Class.forName(driver); 
		 
		 // connect with database
		 Connection conn = DriverManager.getConnection(url, user, password); 
		 
		 if(!conn.isClosed()) 
		 {
			 //System.out.println("Succeeded connecting to the Database!");	
		 }
		 
		 Statement statement = conn.createStatement();
		 
		 String sql = "select * from " + nstabname +"0;";
		 
		 ResultSet rs = statement.executeQuery(sql);
//		 rs.last();
//		 // Get the row number of the last row which is also the row count
//		 int rowCount = rs.getRow();
//		 
//		 ids = new int[rowCount];
//		 rs.first();
//		 int row=0;
		 while(rs.next()){
			 idns.put(""+rs.getInt("nsid"), rs.getString("content"));
		 }
		 
		 conn.close(); 
		 
		 
	 } catch(Exception e) { 
		 
		 e.printStackTrace(); 
	 }
	 
	 return idns;
 }
 
 
 public static HashMap<String, String> getMapNSID(){
	 
	 HashMap<String, String> nsid = new HashMap<String, String>();
	 
	 try { 
		 // load the driver
		 Class.forName(driver); 
		 
		 // connect with database
		 Connection conn = DriverManager.getConnection(url, user, password); 
		 
		 if(!conn.isClosed()) 
		 {
			 //System.out.println("Succeeded connecting to the Database!");	
		 }
		 
		 Statement statement = conn.createStatement();
		 
		 String sql = "select * from " + nstabname +"0;";
		 
		 ResultSet rs = statement.executeQuery(sql);
//		 rs.last();
//		 // Get the row number of the last row which is also the row count
//		 int rowCount = rs.getRow();
//		 
//		 ids = new int[rowCount];
//		 rs.first();
//		 int row=0;
		 while(rs.next()){
			 nsid.put(rs.getString("content"),""+rs.getInt("nsid"));
		 }
		 
		 conn.close(); 
		 
		 
	 } catch(Exception e) { 
		 
		 e.printStackTrace(); 
	 }
	 
	 return nsid;
 }
 public static HashMap<String, String> getMapItemNS(){
	 
	 HashMap<String, String> itemns = new HashMap<String, String>();
	 
	 try { 
		 // load the driver
		 Class.forName(driver); 
		 
		 // connect with database
		 Connection conn = DriverManager.getConnection(url, user, password); 
		 
		 if(!conn.isClosed()) 
		 {
			 //System.out.println("Succeeded connecting to the Database!");	
		 }
		 
		 Statement statement = conn.createStatement();
		 
		 String sql = "select * from " + itemtabname +"0;";
		 
		 ResultSet rs = statement.executeQuery(sql);
//		 rs.last();
//		 // Get the row number of the last row which is also the row count
//		 int rowCount = rs.getRow();
//		 
//		 ids = new int[rowCount];
//		 rs.first();
//		 int row=0;
		 while(rs.next()){
			 itemns.put(""+rs.getInt("id"), rs.getString("content"));
		 }
		 
		 conn.close(); 
		 
		 
	 } catch(Exception e) { 
		 
		 e.printStackTrace(); 
	 }
	 
	 return itemns;
 }

	public static HashMap<String, String> getMapNSMean() {
		HashMap<String, String> nsmean = new HashMap<String, String>();
		 
		 try { 
			 // load the driver
			 Class.forName(driver); 
			 
			 // connect with database
			 Connection conn = DriverManager.getConnection(url, user, password); 
			 
			 if(!conn.isClosed()) 
			 {
				 //System.out.println("Succeeded connecting to the Database!");	
			 }
			 
			 Statement statement = conn.createStatement();
			 
			 String sql = "select * from " + meansdevtabname +";";
			 
			 ResultSet rs = statement.executeQuery(sql);
//			 rs.last();
//			 // Get the row number of the last row which is also the row count
//			 int rowCount = rs.getRow();
//			 
//			 ids = new int[rowCount];
//			 rs.first();
//			 int row=0;
			 while(rs.next()){
				 nsmean.put(""+rs.getInt("nsid"), rs.getString("mean"));
			 }
			 
			 conn.close(); 
			 
			 
		 } catch(Exception e) { 
			 
			 e.printStackTrace(); 
		 }
		 
	return nsmean;
}






	public static void drop(String dtab){
		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}

			Statement statement = conn.createStatement();

			String sql="drop table "+ dtab +" ;" ;
			System.out.println(sql);
			statement.executeUpdate(sql); 

			conn.close(); 

		}
		catch(Exception e) { 

			e.printStackTrace(); 
		}
		
		System.out.println("...continue");
	}
	
	
	public static void createMeanSdevTable(){
		
		try { 
			// load the driver
			Class.forName(driver); 
			
			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 
			
			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}
//			create table ratestable (id int not null, 
			Statement statement = conn.createStatement();
			
			String sql="create table if not exists "+ meansdevtabname +" ( nsid int not null, ";
			
			String commaAndSpace = " float, ";
			sql=sql + "mean"+ commaAndSpace; 
			sql=sql + "sdev"+ commaAndSpace; 
			
			
			sql=sql+"primary key(nsid));";
			System.out.println(sql);
			statement.executeUpdate(sql); 
			
			conn.close(); 
			
		} catch(Exception e) { 
			
			e.printStackTrace(); 
		}
		
		
	}
	public static void saveMeanSDev(float[][] msd)
	{
		try { 
			// load the driver
			Class.forName(driver); 

			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 

			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}
			
			Statement statement;
			String desql="delete from "+meansdevtabname;
			statement= conn.createStatement();
System.out.println("" + desql);
			statement.executeUpdate(desql); 
			

			for(int row=1; row< msd[0].length+1; row++){
				statement= conn.createStatement();

				String sql="insert into "+meansdevtabname+" (nsid";


				String commaAndSpace = ", ";
				sql=sql+commaAndSpace+"mean";
				sql=sql+commaAndSpace+"sdev";
				sql=sql+") values ("+row;
				sql=sql+commaAndSpace+msd[0][row-1];
				sql=sql+commaAndSpace+msd[1][row-1];
				sql=sql+");";
//				for(int i=0; i< msd[0].length; i++)
//				{
//					sql=sql+commaAndSpace+msd[row-1][i];
//					sql=sql+commaAndSpace+msd[row-1][i];
//				}
//



			//	System.out.println(sql);
				statement.executeUpdate(sql); 
			}
			conn.close(); 

		} catch(Exception e) { 

			e.printStackTrace(); 
		}
	}
	
	public static float[][] loadMeanSdev(){
		

		float[][] vT = null;
		try { 
			// load the driver
			Class.forName(driver); 
			
			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 
			
			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}
			
			PreparedStatement statement = conn.prepareStatement(
			        "SELECT * FROM "+meansdevtabname,  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		//	updateSales.setInt(1, 75); 
		//	updateSales.setString(2, "Colombian"); 
		//	updateSales.executeUpdate():
		//			Statement statement = conn.createStatement();
		//			String sql="select * from "+soltabname + " where id = " + id +";";
		//			statement.setString(1, soltabname);
//					statement.setInt(1,	id);
					ResultSet rs = statement.executeQuery();
		//			int row=0;
					ResultSetMetaData rsmd = rs.getMetaData();
					int noOfColumns = rsmd.getColumnCount();
					// Create a scrollable result set
				    
					
//					Statement stmt = connection.createStatement(
//				        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//				    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

				    // Move to the end of the result set
				    rs.last();
				    // Get the row number of the last row which is also the row count
				    int rowCount = rs.getRow();
				    rs.beforeFirst();
					vT = new float[noOfColumns-1][rowCount];
					int row=0;
					while(rs.next()){
						for(int i=2; i<noOfColumns+1; i++){
							vT[i-2][row]=(rs.getFloat(i));
						}
						row++;
		//				System.out.println("a is "+a);
					}
//			Statement statement = conn.createStatement();
//			String sql="select * from "+meansdevtabname;
//			ResultSet rs = statement.executeQuery(sql);
//			
//			int row=0;
//			
//			while(rs.next())
//			{
//				for(int i=0; i<numV; i++){
//					
//					vT[row][i]=rs.getObject(i+2);
//				}
//				row++;
//			}
//			
			conn.close(); 
		
			
		} catch(Exception e) { 
			
			e.printStackTrace(); 
		}
		
		return vT;
		
	}
	

	
//	public static void main(String[] args) {
//		createBrokersTab();
////		outputSol(1111);
////		createMeanSdevTable();
//	}






	public static void createNSTab(int tabnum) {
		try { 
			// load the driver
			Class.forName(driver); 
			
			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 
			
			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}
//			create table ratestable (id int not null, 
			Statement statement = conn.createStatement();
//			create table namespace0(nsid int not null, content varchar(60),primary key(nsid));
			String sql="create table if not exists "+ nstabname+ "0"+" ( nsid int not null,  content varchar(60),primary key(nsid));";
			
//			String commaAndSpace = " float, ";
//			sql=sql + "mean"+ commaAndSpace; 
//			sql=sql + "sdev"+ commaAndSpace; 
//			
			
//			sql=sql+"primary key(nsid));";
			System.out.println(sql);
			statement.executeUpdate(sql); 
			
			System.out.println("delete from " + nstabname + tabnum);
			statement.execute("delete from " + nstabname + tabnum);
			conn.close(); 
			
		} catch(Exception e) { 
			
			e.printStackTrace(); 
		}		
	}
	
	public static void createItemTab(int tabnum) {
		try { 
			// load the driver
			Class.forName(driver); 
			
			// connect with database
			Connection conn = DriverManager.getConnection(url, user, password); 
			
			if(!conn.isClosed()) 
			{
				//System.out.println("Succeeded connecting to the Database!");	
			}
//			create table ratestable (id int not null, 
			Statement statement = conn.createStatement();
//			create table item0(id int not null, content varchar(60), primary key(id));
			String sql="create table if not exists "+ itemtabname+ tabnum+" ( id int not null,  content varchar(60),primary key(id));";
			
//			String commaAndSpace = " float, ";
//			sql=sql + "mean"+ commaAndSpace; 
//			sql=sql + "sdev"+ commaAndSpace; 
//			
			
//			sql=sql+"primary key(nsid));";
			System.out.println(sql);
			statement.executeUpdate(sql); 
			
			conn.close(); 
			
		} catch(Exception e) { 
			
			e.printStackTrace(); 
		}		
	}
	
}	
